﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using HK2009.BusinessFacade;
using HK2009.EntityManage;
using HK2009.Common;
using HK2009.EntityQuery;
using System.Collections;

namespace HK2009.BusinessFacadeHandler
{
    public class BNBookingHandler : IBNBookingHandler
    {
        DataBase db = new DataBase();
        BN_BookingManage manager = new BN_BookingManage();

        BN_BookingDtlManage dtlManager = new BN_BookingDtlManage();

        /// <summary>
        /// 根据sql语句同参数获取列表
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public DataTable GetList(string sqlStr, SqlParameter[] parms)
        {
            return db.RunSqlTab(sqlStr, parms);
        }


        public bool BookingAdd(string sqlStr, SqlParameter[] parms, ref int bookingid)
        {
            return db.ExecSqlTransaction(sqlStr, parms, ref bookingid);
        }

        public bool UpdateBookingStatus(string sqlStr, SqlParameter[] parms)
        {
            return db.ExecSqlTransaction(sqlStr, parms);
        }

        public BN_BookingQuery GetBookingByID(int pkid)
        {
            return manager.GetBN_BookingQuery(pkid);
        }

        public BN_BookingDtlQuery GetBookingDtlByDtlID(int dtlId)
        {
            return dtlManager.GetBN_BookingDtlQuery(dtlId);
        }

        public bool BookingArrange(string[] strSql, ArrayList parms)
        {
            return db.ExecSqlTransaction(strSql, parms);
        }

        public bool BookingArrange2(string strSql, SqlParameter[] parms)
        {
            return db.ExecSqlTransaction(strSql, parms);
        }

        public bool BookingCancelArrange(string strSql, SqlParameter[] parms)
        {
            return db.ExecSqlTransaction(strSql, parms);
        }

        public bool UpdateDetail(int id, string jobDate, string user, object userId, bool isCompanyUser)
        {
            BN_BookingDtlQuery dtlQuery = dtlManager.GetBN_BookingDtlQuery(id);
            BN_BookingQuery bookingQuery = manager.GetBN_BookingQuery(dtlQuery.BookingID);
            DateTime? jd = null;

            if (!"".Equals(jobDate.Trim()))
            {
                jd = DateTime.Parse(jobDate);
            }
            
            // 如果修改了收货日期
            if (dtlQuery.JobDate != jd)
            {
                List<string> emailList = new List<string>();
                string sqlStr;
                SqlParameter[] parms;
                int i = 0;

                if (jd == null)
                {
                    parms = new SqlParameter[2];
                    sqlStr = @"UPDATE BN_BookingDtl SET JobDate = null WHERE PKID = @BookingDtlID;
                                  UPDATE BN_BookingNoteDtl SET JobDate = null WHERE PKID = @BNDtlID;
                                  UPDATE BN_ShippingOrderDtl SET JobDate = null WHERE BookingDtlID = @BookingDtlID;
                                ";
                }
                else
                {
                    parms = new SqlParameter[3];
                    sqlStr = @"UPDATE BN_BookingDtl SET JobDate = @JobDate WHERE PKID = @BookingDtlID;
                                  UPDATE BN_BookingNoteDtl SET JobDate = @JobDate WHERE PKID = @BNDtlID;
                                  UPDATE BN_ShippingOrderDtl SET JobDate = @JobDate WHERE BookingDtlID = @BookingDtlID;
                                ";
                    parms[0] = new SqlParameter("@JobDate", jd);
                    i++;
                }

                parms[i] = new SqlParameter("@BookingDtlID",dtlQuery.PKID);
                parms[i + 1] = new SqlParameter("@BNDtlID", dtlQuery.BookingNoteDtlID);

                db.ExecSqlTransaction(sqlStr, parms);

                DataTable dtEmail;

                // 如果是公司用户，则发email给对应的运输队和工厂，如果是工厂用户，则只发给公司对应的联系人

                if (isCompanyUser)
                {
                    // 读取工厂联系人email
                    sqlStr = @"SELECT lm.* FROM Sy_ClientLinkMan lm
                                    INNER JOIN BN_BookingNoteDtl bnDtl ON lm.PKID = bnDtl.VendorLinkMan
                                WHERE bnDtl.PKID = @BNDtlID";
                    parms = new SqlParameter[1];
                    parms[0] = new SqlParameter("@BNDtlID", dtlQuery.BookingNoteDtlID);
                    dtEmail = db.RunSqlTab(sqlStr, parms);
                    if (dtEmail != null && dtEmail.Rows.Count > 0)
                    {
                        foreach (DataRow row in dtEmail.Rows)
                        {
                            emailList.Add(row["Email"].ToString());
                        }
                    }

                    // 如果已分配，读取运输队所有联系人的email
                    if (bookingQuery.Status == 1)
                    {
                        sqlStr = "SELECT * FROM Sy_ClientLinkMan WHERE ClientID = @Contractor";
                        parms = new SqlParameter[1];
                        parms[0] = new SqlParameter("@Contractor", dtlQuery.Contractor);
                        dtEmail = db.RunSqlTab(sqlStr, parms);
                        if (dtEmail != null && dtEmail.Rows.Count > 0)
                        {
                            foreach (DataRow row in dtEmail.Rows)
                            {
                                emailList.Add(row["Email"].ToString());
                            }
                        }
                    }
                }
                else
                {
                    // 读取公司联系人email
                    sqlStr = @"SELECT users.*
                               FROM Users users INNER JOIN
                                    Sy_Client ON users.UserID = Sy_Client.CompanyBP
                               WHERE Sy_Client.PKID = @VendorID";
                    parms = new SqlParameter[1];
                    parms[0] = new SqlParameter("@VendorID", bookingQuery.VendorID);
                    dtEmail = db.RunSqlTab(sqlStr, parms);
                    if (dtEmail != null && dtEmail.Rows.Count > 0)
                    {
                        foreach (DataRow row in dtEmail.Rows)
                        {
                            emailList.Add(row["Email"].ToString());
                        }
                    }
                }

                // 读取自己的email
                sqlStr = "SELECT * FROM Users WHERE UserID = @UserID";
                parms = new SqlParameter[1];
                parms[0] = new SqlParameter("@UserID", userId);
                dtEmail = db.RunSqlTab(sqlStr, parms);
                if (dtEmail != null && dtEmail.Rows.Count > 0)
                {
                    foreach (DataRow row in dtEmail.Rows)
                    {
                        emailList.Add(row["Email"].ToString());
                    }
                }

                // 发送email
                string template = @"


[list]


                                    ";
                string dateFormat = "yyyy-MM-dd HH:mm:ss";
                string change = "BookingNO:" + bookingQuery.BookingNO + @"

";
                change = change + "ItemNO:" + dtlQuery.ItemNO + @"

";
                change = change + "SONO:" + dtlQuery.SONO + @"

";
                change = change + "PNNO:" + dtlQuery.PNNO + @"

";
                change = change + "Edited by:" + user + @"

";
                if (dtlQuery.JobDate != jd)
                {
                    if (jd != null)
                    {
                        change += "changed JobDate to:" + ((DateTime)jd).ToString(dateFormat) + @"

";
                    }
                    else
                    {
                        change += "changed JobDate to: null";
                    }
                }
                template = template.Replace("[list]", change);

                ISyAutoSendEmail autoEmail = new SyAutoSendEmail();
                autoEmail.InsertEmailContent("Update Booking", template, emailList, int.Parse(userId.ToString()));

                //string subject = "reminds from special-line!";
                //foreach (string email in emailList)
                //{
                //    if (StringUtil.IsEmailAddress(email))
                //    {
                //        SendMail send = new SendMail();
                //        string content = template.Replace("[title]", email);
                //        send.MailBody = content;
                //        send.MailSubject = subject;
                //        send.MailTo = email;

                //        send.Send();
                //    }
                //}
            }
            return true;
        }

        public DataTable GetBookingDtlByBookingNoteDtlID(int bookingnoteDtlID)
        {
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = new SqlParameter("@BookingNoteDtlID", bookingnoteDtlID.ToString());
            return dtlManager.GetBN_BookingDtlList("BookingNoteDtlID=@BookingNoteDtlID", parms);
        }

        public string GetAppointament(string jobDate)
        {
            string sql = @"SELECT dbo.Fn_NextAppointment(@JobDate)";
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = new SqlParameter("@JobDate", jobDate);
            object tmp = db.RunSql(sql, parms, true);
            
            return tmp.ToString();
        }

        public object GetObjectBySql(string sql, SqlParameter[] parms)
        {
            return db.RunSql(sql, parms, true);
        }
    }
}